package com.kelaite.procurement.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.kelaite.procurement.entity.TCity;
import com.kelaite.procurement.entity.TCompany;
import com.kelaite.procurement.entity.TIndustry;
import com.kelaite.procurement.entity.TMaterialType;
import com.kelaite.procurement.entity.TOrganizationType;
import com.kelaite.procurement.entity.TProvince;
import com.kelaite.procurement.entity.TUnit;
import com.kelaite.procurement.entity.TUser;

public class ExcelUtils {
	
	private static String[] TUnitExcel={"unit_name","unit_type"};
	
	private static String[] TMaterialTypeExcel={"material_type_name","parent_id"};
	
	private static String[] TMaterialMasterCentralExcel={"material_name","material_pic","unit","material_type_id"};
	
	private static String[] TIndustryExcel={"industry_name","industry_desc"};
	
	private static String[] TCompanyExcel={"business_id","company_name","business_type","organization_code","tax_no","biz_license_pic",
				"org_code_pic","tax_registration_cert_pic","is_general_taspayer","legal_representative","province_id","city_id",
				"address","postal_code","org_type","registered_capital","bank_name","bank_account","registered_phone","main_business",
				"group_id","is_deleted","description","extra_info","branch"};
	
	private static String[] TUserExcel={"real_name","company_id","username","password","access_token","language","phone","mobile_phone",
				"wechat","email","type","role_ids","is_delete","created_by","updated_by","is_company_admin"};
	
	public static void ExportExcelFile(String fileName)
	{		
		XSSFWorkbook workBook=new XSSFWorkbook();
		XSSFSheet sheet=workBook.createSheet();
	
		String[] temp=null;
		//创建第一行,表头
		XSSFRow row=sheet.createRow(0);
		if(fileName.contains("TUnit"))
		{
			temp=TUnitExcel;
		}
		else if(fileName.contains("TMaterialType"))
		{
			temp=TMaterialTypeExcel;
		}
		else if(fileName.contains("TMasterialMasterCentral"))
		{
			temp=TMaterialMasterCentralExcel;
		}
		else if(fileName.contains("TIndustry"))
		{
			temp=TIndustryExcel;
		}
		else if(fileName.contains("TCompany"))
		{
			temp=TCompanyExcel;
		}
		else if(fileName.contains("TUser"))
		{
			temp=TUserExcel;
		}
			
		for(int i=0;i<temp.length;i++)
		{
			//创建单元格
			XSSFCell cell=row.createCell(i);
			cell.setCellValue(temp[i]);
		}
		
		try
		{
			FileOutputStream out=new FileOutputStream("D://TUser.xlsx");
			workBook.write(out);
			out.close();
		}
		catch(Exception e)
		{
			
		}
	}
	
	public static Object ReadExcelFile(MultipartFile file) throws IOException
	{
		String FilePath=file.getOriginalFilename();
		//InputStream is=new FileInputStream("D://123.xlsx");
		InputStream is=file.getInputStream();
		XSSFWorkbook workBook=new XSSFWorkbook(is);
		
		XSSFSheet sheet=workBook.getSheetAt(0);
		if(sheet!=null)
		{
			return saveExcelData(FilePath,sheet);
		}
		return null;
	}
	
	private static Object saveExcelData(String filePath,XSSFSheet sheet)
	{
		
		//保存的是TMaterialType表
		if(filePath.contains("TMaterialType"))
		{
			List<TMaterialType> materialTypeList=new ArrayList<TMaterialType>();
			for(int rowNum=1;rowNum<sheet.getLastRowNum();rowNum++)
			{
				TMaterialType materialType=new TMaterialType();
				XSSFRow row=sheet.getRow(rowNum);
				Object tempObj = getSSFCellValue(row,0);
				materialType.setMaterialTypeName(tempObj.toString());
				tempObj = getSSFCellValue(row,1);
				TMaterialType parentMaterialType=new TMaterialType();
				//parentMaterialType.setId(Math.round(row.getCell(1).getNumericCellValue()));
				parentMaterialType.setId((Long)tempObj);
				materialType.setParentMaterialType(parentMaterialType);

				materialTypeList.add(materialType);
			}
			return materialTypeList;
		}
		//保存的是TUnit表
		else if(filePath.contains("TUnit"))
		{
			List<TUnit> unitList=new ArrayList<TUnit>();
			for(int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++)
			{
				TUnit unit=new TUnit();
				XSSFRow row=sheet.getRow(rowNum);
				
				Object tempObj=getSSFCellValue(row,0);
				unit.setUnitName(tempObj.toString());
				tempObj = getSSFCellValue(row,1);
				unit.setUnitType(Integer.parseInt(tempObj.toString()));

				unitList.add(unit);
			}
			return unitList;
		}
		else if(filePath.contains("TIndustry"))
		{
			List<TIndustry> industryList=new ArrayList<TIndustry>();
			for(int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++)
			{
				TIndustry industry=new TIndustry();
				XSSFRow row=sheet.getRow(rowNum);
				Object tempObj = getSSFCellValue(row,0);
				industry.setIndustryName(tempObj.toString());
				tempObj=getSSFCellValue(row,1);
				industry.setIndustryDesc(tempObj.toString());
				
				industryList.add(industry);
			}
			return industryList;
		}
		else if(filePath.contains("TCompany"))
		{
			List<TCompany> companyList=new ArrayList<TCompany>();
			for(int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++)
			{
				TCompany tCompany=new TCompany();
				XSSFRow row=sheet.getRow(rowNum);
				Object tempObj = getSSFCellValue(row,0);
				tCompany.setBusinessId(tempObj.toString());
				tempObj = getSSFCellValue(row,1);
				tCompany.setCompanyName(tempObj.toString());
				tempObj = getSSFCellValue(row,2);
				//int tempBussType=((Double)tempObj).intValue();
				int tempBussType=((Long)tempObj).intValue();
				short tempBusinessType=(short)(tempBussType);
				tCompany.setBusinessType(tempBusinessType);
				tempObj = getSSFCellValue(row,3);
				tCompany.setOrganizationCode(tempObj.toString());
				tempObj = getSSFCellValue(row,4);
				tCompany.setTaxNo(tempObj.toString());
				tempObj = getSSFCellValue(row,5);
				tCompany.setBizLicensePic(tempObj.toString());
				tempObj = getSSFCellValue(row,6);
				tCompany.setOrgCodePic(tempObj.toString());
				tempObj = getSSFCellValue(row,7);
				tCompany.setTaxRegistrationCertPic(tempObj.toString());
				tempObj = getSSFCellValue(row,8);
				//int tempGen=((Double)tempObj).intValue();
				int tempGen=((Long)tempObj).intValue();
				short tempGeneral=(short)tempGen;
				tCompany.setIsGeneralTaspayer(tempGeneral);
				tempObj = getSSFCellValue(row,9);
				tCompany.setLegalRepresentative(tempObj.toString());
				
				tempObj = getSSFCellValue(row,10);
				//long tempProvince=((Double)tempObj).longValue();
				TProvince tProvince=new TProvince();
				tProvince.setId((Long)tempObj);
				tCompany.settProvince(tProvince);
				
				tempObj = getSSFCellValue(row,11);
				//long tempCity=((Double)tempObj).longValue();
				TCity tCity=new TCity();
				tCity.setId((Long)tempObj);
				tCompany.settCity(tCity);
				
				tempObj = getSSFCellValue(row,12);
				tCompany.setAddress(tempObj.toString());
				
				tempObj = getSSFCellValue(row,13);
				tCompany.setPostalCode(tempObj.toString());
				
				tempObj = getSSFCellValue(row,14);
				//long tempOrgId=((Double)tempObj).longValue();
				TOrganizationType organizationType=new TOrganizationType();
				organizationType.setId((Long)tempObj);
				tCompany.settOrganizationType(organizationType);
				
				tempObj = getSSFCellValue(row,15);
				//long tempRegisteredCaptial=((Double)tempObj).longValue();
				tCompany.setRegisteredCapital((Long)tempObj);
				tempObj = getSSFCellValue(row,16);
				tCompany.setBankName(tempObj.toString());
				tempObj = getSSFCellValue(row,17);
				tCompany.setBankAccount(tempObj.toString());
				tempObj = getSSFCellValue(row,18);
				tCompany.setRegisteredPhone(tempObj.toString());
				tempObj = getSSFCellValue(row,19);
				tCompany.setMainBusiness(tempObj.toString());
				tempObj = getSSFCellValue(row,20);
				//tCompany.setGroupId(((Double)tempObj).longValue());
				tCompany.setGroupId((Long)tempObj);
				tempObj = getSSFCellValue(row,21);
				tCompany.setIsDeleted(tempObj.toString());
				tempObj = getSSFCellValue(row,22);
				tCompany.setDescription(tempObj.toString());
				tempObj = getSSFCellValue(row,23);
				tCompany.setExtraInfo(tempObj.toString());
				tempObj = getSSFCellValue(row,24);
				tCompany.setBranch(tempObj.toString());
				
				companyList.add(tCompany);				
			}
			return companyList;
		}
		else if(filePath.contains("TUser"))
		{
			List<TUser> userList=new ArrayList<TUser>();
			for(int rowNum=1;rowNum<=sheet.getLastRowNum();rowNum++)
			{
				TUser tUser=new TUser();
				XSSFRow row=sheet.getRow(rowNum);
				Object tempObj = getSSFCellValue(row,0);
				tUser.setRealName(tempObj.toString());
				
				tempObj = getSSFCellValue(row,1);
				TCompany tCompany=new TCompany();
				//tCompany.setId(((Double)tempObj).longValue());
				tCompany.setId((Long)tempObj);
				tUser.settCompany(tCompany);
				
				tempObj = getSSFCellValue(row,2);
				tUser.setUsername(tempObj.toString());
				tempObj = getSSFCellValue(row,3);
				tUser.setPassword(tempObj.toString());
				tempObj = getSSFCellValue(row,4);
				tUser.setAccessToken(tempObj.toString());
				tempObj = getSSFCellValue(row,5);
				tUser.setLanguage(tempObj.toString());
				tempObj = getSSFCellValue(row,6);
				tUser.setPhone(tempObj.toString());
				tempObj = getSSFCellValue(row,7);
				tUser.setMobilePhone(tempObj.toString());
				tempObj = getSSFCellValue(row,8);
				tUser.setWechat(tempObj.toString());
				tempObj = getSSFCellValue(row,9);
				tUser.setEmail(tempObj.toString());
				tempObj = getSSFCellValue(row,10);
				//int tempType=((Double)tempObj).intValue();
				int tempType=((Long)tempObj).intValue();
				tUser.setType((short)tempType);
				tempObj = getSSFCellValue(row,11);
				tUser.setRoleIds(tempObj.toString());
				
				tempObj = getSSFCellValue(row,12);
				//tempType=((Double)tempObj).intValue();
				tempType=((Long)tempObj).intValue();
				tUser.setIsDelete((short)tempType);
						
				tUser.setGmtCreateTime(new Date());
				tUser.setGmtUpdateTime(new Date());
				
				tempObj = getSSFCellValue(row,13);
				tUser.setCreatedBy(tempObj.toString());
				tempObj = getSSFCellValue(row,14);
				tUser.setUpdatedBy(tempObj.toString());
				tempObj = getSSFCellValue(row,15);
				//tUser.setIsCompanyAdmin(Integer.valueOf(((Double)tempObj).intValue()));
				tUser.setIsCompanyAdmin(Integer.parseInt(tempObj.toString()));
				
				userList.add(tUser);
			}
			return userList;
		}
		return null;
	}
	
	private static Object getSSFCellValue(XSSFRow row,int cellNum){
	
		XSSFCell cell=row.getCell(cellNum);
		Object tempObj=null;
		switch(cell.getCellType())
		{
			case XSSFCell.CELL_TYPE_STRING:
				tempObj=cell.getStringCellValue();
				break;
			case XSSFCell.CELL_TYPE_NUMERIC:
				tempObj=cell.getNumericCellValue();
				tempObj=((Double)tempObj).longValue();
				break;
		}
		return tempObj;
	}
	
	
	public static void main(String[] args) throws IOException {
		
		ExportExcelFile("TUser");
		//ReadExcelFile("TUnit");
		
//		try {
//			ReadExcelFile(null);
//		} catch (IOException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
	}

}
